﻿Imports System.Web.Services
Imports System.Web.Services.Protocols
Imports System.ComponentModel
Imports DB
Imports WebDummy
Imports Logging
Imports System.Linq
Imports System.Data.Linq
Imports System.Data.DataTableExtensions
Imports System.IO
Imports System.Xml.Serialization

' To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
' <System.Web.Script.Services.ScriptService()> _
<System.Web.Services.WebService(Namespace:="http://tempuri.org/")> _
<System.Web.Services.WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _
<ToolboxItem(False)> _
Public Class GetData
    Inherits System.Web.Services.WebService

    Dim DB As DB.Connection
    Dim list As New List(Of Object)
    Dim msg As String
    Dim params As New ArrayList
    Dim logger As New Logging.Logging("Log.txt")
    Dim ds As New DataSet



#Region "Laad een aantal basisgegevens in, zoals de Singleton klasse v Connectie"
    Private Sub LoadBasics()
        DB = Connection.getInstance
        list.Clear()
        params.Clear()
        ds.Clear()
    End Sub
#End Region
#Region "Omzetting DataSet nr List(Of Object)"
    Private Function DataSetToListOfObject(ByVal dataset As DataSet) As List(Of Object)
        Dim table As New DataTable
        Dim data As New List(Of Object)

        For Each row As DataRow In dataset.Tables(0).Rows
            For Each s As Object In row.ItemArray
                data.Add(s)
            Next
        Next

        DataSetToListOfObject = data
    End Function
#End Region

#Region "Structures speciale datatypen"
    Public Structure Users
        Dim PID As Integer
        Dim Name As String
        Dim Tribe As Integer
        Dim Pop As Integer
        Dim Alliance As String
    End Structure

    Public Structure Races
        Dim RaceID As Integer
        Dim RaceName As String
    End Structure

    Public Structure Units
        Dim Unit_ID As Integer
        Dim Unit_Name As String
        Dim Unit_Race As String
        Dim Unit_Speed As Integer
        Dim Loot As Integer
        Dim Funct As Char
        Dim Type As Char
    End Structure

    Public Structure Commands
        Dim Command_Name As String
        Dim Access_lvl As Integer
    End Structure

    Public Structure Village
        Dim VID As Integer
        Dim Name As String
        Dim X As Integer
        Dim Y As Integer
        Dim Distance As Integer
    End Structure
    Public Structure Alliance
        Dim AID As Integer
        Dim Name As String
    End Structure

    Public Structure Alliance_Stats
        Dim Name As String
        Dim Pop As Integer
        Dim Top As Integer
        Dim Floor As Integer
        Dim Members As Integer
    End Structure
#End Region

#Region "Possible Actions (SP with parameters)"
    ' ''Possible actions 
    ''    Create_Database       : Dropt huidige DB, maakt nieuwe DB aan ('Travian') met tabellen, views, stored procedures en standaard data (InsertData.sql)
    ''    Get_StoredProcedures  : Geeft de namen terug van de Stored Procedures
    '     Get_Views             : Geeft de namen terug van de Views
    ''    Command_Insert        : Voegt een command toe a/d tabel commands (IN : Command_Name varchar(12), Access_lvl int)
    '     Command_Delete        : Delete een command in de tabel commands (IN : Cmd_Name varchar(12))
    '     Get_Members           : Geeft members v/e alliantie terug (IN : Alliance varchar(12)
    '     Get_Member            : Geeft info over 1 member terug (IN : @Member varchar(20))
    '     Get_Units             : Geeft units v/e ras terug (IN : Race varchar(11)
    '     Get_Races             : Geeft alles rassen terug als een List(Of Race)
    '     Get_Ally_Stats        : Geeft data terug ivm alliantie (IN : Ally varchar(20)
    '     Get_Villages          : Geeft data terug ivm dorpen v/e speler (IN : Player varchar(20)
    '     Populate_Members      : Vult tabel members met waarden uit de view Players (IN : @Alliance VARCHAR(20))
    '     Update_Priveleged_Member : Update de Access_lvl van een speler in de tabel priveleged_members (IN : Access_lvl integer, @Name VARCHAR(20))
    '     Web_CheckIfUserExists : Checkt of de WebUserName al bestaat in de tabel website_users (IN : Username VARCHAR(20))
    '     Web_GetPassword       : Haalt paswoord uit tabel website_users (IN: @Username VARCHAR(20))
    '     Web_RegisterUser      : Voegt een record met WebUser gegevens toe ad tabel website_users (IN : @UserName VARCHAR(20), @Password VARCHAR(20), _
    '                               @Email VARCHAR(50), @FullName VARCHAR(50), @Country VARCHAR(20))
    '     Get_Commands          : Geeft alle commands van de tabel commands waar het Access_lvl voldoet aan de meegegeven parameter. (IN : @Access_lvl integer)
    '     CheckIfMemberExists   : Geeft het PlayerID (Int) terug v/e speler in travian DB, Indien speler niet bestaat, geeft 9999 terug (IN : speler string)

#End Region


#Region "WebMethod Create DB"
    'Aanmaken DB (+SP, Views, Tabellen en Insert std waarden
    <WebMethod()> Public Function CreateDB() As String
        'Create_Database (vr meer info check DB.CreateDB)
        Try
            LoadBasics()
            DB.CreateDB()
            msg = "Database aangemaakt !"
        Catch ex As Exception
            logger.WriteLine("WebMethod Create_DB", ex.ToString)
            CreateDB = "Fout bij het aanmaken van de Database, check Log.txt"
            Exit Function
        End Try

        CreateDB = msg
    End Function
#End Region
#Region "WebMethod Get_StoredProcedures"
    'Get_StoredProcedures
    <WebMethod()> Public Function Get_StoredProcedures(ByVal sp As String) As List(Of Object)

        Try
            LoadBasics()
            list = DataSetToListOfObject(DB.GetListOf("StoredProcedures"))
        Catch ex As Exception
            logger.WriteLine("WebMethod Get_StoredProcedures", ex.ToString)
            Get_StoredProcedures = Nothing
            Exit Function
        End Try
        Get_StoredProcedures = list
    End Function

#End Region
#Region "WebMethod Get_Views"
    'Get_Views
    <WebMethod()> Public Function Get_Views(ByVal sp As String) As List(Of Object)

        Try
            LoadBasics()
            list = DataSetToListOfObject(DB.GetListOf("Views"))
        Catch ex As Exception
            logger.WriteLine("WebMethod Get_Views", ex.ToString)
            Get_Views = Nothing
            Exit Function
        End Try
        Get_Views = list
    End Function

#End Region
#Region "WebMethod Command_Insert"
    'Command_Insert
    <WebMethod()> Public Function Command_Insert(ByVal command As String, access_lvl As Integer) As String



        Try
            LoadBasics()
            With params
                .Add(command)
                .Add(access_lvl)
            End With
            DB.GetStoredProcedure("Command_Insert", params)
            msg = "Commando " & command & " is succesvol toegevoegd"
        Catch ex As Exception
            msg = "Mislukt " & command & " toe te voegen : " & ex.ToString
            logger.WriteLine("WebMethod Command_Insert", ex.ToString)
        End Try
        Command_Insert = msg
    End Function
#End Region
#Region "WebMethod Get_Member"
    <WebMethod(), XmlInclude(GetType(Users))> Public Function Get_Member(ByVal player As String) As List(Of Object)

        Try
            LoadBasics()
            params.Add(player)
            ds = DB.GetStoredProcedure("Get_Member", params)

            If (ds.Tables.Count > 0) Then
                Dim member As Users
                For Each Row As DataRow In ds.Tables(0).Rows
                    member.PID = Row.Item("PID")
                    member.Name = Row.Item("Name")
                    member.Tribe = Row.Item("Tribe")
                    member.Pop = Row.Item("Pop")
                    member.Alliance = Row.Item("Alliance")
                    list.Add(member)
                Next
            End If
        Catch ex As Exception
            logger.WriteLine("WebMethod Get_Member", ex.ToString)
            Get_Member = Nothing
            Exit Function
        End Try
        Get_Member = list


    End Function
#End Region

#Region "WebMethod CheckIfMemberExists"
    <WebMethod()> Public Function CheckIfMemberExists(ByVal player As String) As Integer

        Dim member As New List(Of Object)
        member = Get_Member(player)


        If (member.Count > 0) Then
            CheckIfMemberExists = member.Item(0).PID
        Else
            CheckIfMemberExists = 9999
        End If

    End Function
#End Region

#Region "WebMethod Get_Members"
    'Get_Members
    <WebMethod(), XmlInclude(GetType(Users))> Public Function Get_Members(ByVal alliance As String) As List(Of Object)


        Try
            LoadBasics()
            params.Add(alliance)
            ds = DB.GetStoredProcedure("Get_Members", params)

            If (ds.Tables.Count > 0) Then
                If (ds.Tables(0).Columns.Count > 0) Then
                    Dim Member As New Users
                    For Each Row As DataRow In ds.Tables(0).Rows
                        Member.PID = Row.Item("PID")
                        Member.Name = Row.Item("Name")
                        Member.Tribe = Row.Item("Tribe")
                        Member.Pop = Row.Item("Pop")
                        Member.Alliance = Row.Item("Alliance")
                        list.Add(Member)
                    Next
                End If
            End If
        Catch ex As Exception
            logger.WriteLine("WebMethod Get_Members", ex.ToString)
            Get_Members = New List(Of Object)
            Exit Function
        End Try

        Get_Members = list
    End Function
#End Region
#Region "WebMethod Get_Units"
    <WebMethod(), XmlInclude(GetType(Units))> Public Function Get_Units(ByVal race As String) As List(Of Object)
        Dim unit As Units


        Try
            LoadBasics()
            params.Add(race)
            ds = DB.GetStoredProcedure("Get_Units", params)
            For Each Row As DataRow In ds.Tables.Item(0).Rows
                unit.Unit_ID = Row.Item("Unit_ID")
                unit.Unit_Name = Row.Item("Unit_Name")
                unit.Unit_Race = Row.Item("Unit_Race")
                unit.Unit_Speed = Row.Item("Unit_Speed")
                unit.Loot = Row.Item("Loot")
                unit.Funct = Row.Item("Funct")
                unit.Type = Row.Item("Type")
                list.Add(unit)
            Next
        Catch ex As Exception
            logger.WriteLine("WebMethod Get_Units", ex.ToString)
            Get_Units = Nothing
            Exit Function
        End Try
        Get_Units = list

    End Function
#End Region
#Region "WebMethod Get_Races"
    'Get_Races
    <WebMethod(), XmlInclude(GetType(Races))> Public Function Get_Races() As List(Of Object)
        Dim race As New Races
        Try
            LoadBasics()
            ds = DB.GetStoredProcedure("Get_Races")
            For Each Row As DataRow In ds.Tables.Item(0).Rows
                race.RaceID = Row.Item("Race_id")
                race.RaceName = Row.Item("Race_name")
                list.Add(race)
            Next
        Catch ex As Exception
            logger.WriteLine("WebMethod Get_Races", ex.ToString)
            Get_Races = Nothing
            Exit Function
        End Try

        Get_Races = list

    End Function
#End Region
#Region "Webmethod Get_Ally_Stats"
    'Get_Ally_Stats
    <WebMethod(), XmlInclude(GetType(Alliance_Stats))> Public Function Get_Ally_Stats(ByVal AID As Integer) As List(Of Object)


        Try
            LoadBasics()
            params.Add(AID)
            ds = DB.GetStoredProcedure("Get_Ally_Stats", params)
            Dim Stats As New Alliance_Stats
            For Each Row As DataRow In ds.Tables.Item(0).Rows
                Stats.Name = Row.Item("Name")
                Stats.Top = Row.Item("Top")
                Stats.Members = Row.Item("Members")
                Stats.Pop = Row.Item("Pop")
                Stats.Floor = Row.Item("Min")
                list.Add(Stats)
            Next
        Catch ex As Exception
            logger.WriteLine("WebMethod Get_Ally_Stats", ex.ToString)
            Get_Ally_Stats = Nothing
            Exit Function
        End Try

        Get_Ally_Stats = list
    End Function


#End Region
#Region "WebMethod Get_Villages"
    'Get_Villages
    <WebMethod(), XmlInclude(GetType(Village))> Public Function Get_Villages(ByVal player As String) As List(Of Object)
        Try
            LoadBasics()
            params.Add(player)
            ds = DB.GetStoredProcedure("Get_Villages", params)
            Dim Village As New Village
            For Each Row As DataRow In ds.Tables.Item(0).Rows

                Village.Name = Row.Item("Name")
                Village.VID = Row.Item("VID")
                Village.X = Row.Item("X")
                Village.Y = Row.Item("Y")
                Village.Distance = Row.Item("Distance")
                list.Add(Village)
            Next
        Catch ex As Exception
            logger.WriteLine("WebMethod Get_Villages", ex.ToString)
            Get_Villages = Nothing
            Exit Function
        End Try

        Get_Villages = list
    End Function
#End Region
#Region "WebMethod Populate_Members"
    'Populate_Members
    <WebMethod()> Public Function Populate_Members(ByVal alliance As String) As String


        Try
            LoadBasics()
            params.Add(alliance)
            DB.GetStoredProcedure("Populate_Members", params)
            msg = "Populate_members gelukt"
        Catch ex As Exception
            msg = "Populate_members mislukt : Check Log.txt"
            logger.WriteLine("WebMethod Populate_Members", ex.ToString)
        End Try

        Populate_Members = msg
    End Function
#End Region
#Region "WebMethod Update_Priveleged_Member"
    <WebMethod()> Public Function Update_Priveleged_Member(ByVal player As String, ByVal access_lvl As Integer) As String

        Try
            LoadBasics()
            With params
                .Add(player)
                .Add(access_lvl)
            End With
            DB.GetStoredProcedure("Update_Priveleged_Member", params)
            msg = "Update_Priveleged_Member gelukt"
        Catch ex As Exception
            msg = "Update player " & player & "naar access_lvl " & access_lvl & " mislukt : Check Log.txt"
            logger.WriteLine("WebMethod Update_Priveleged_Member", ex.ToString)
        End Try

        Update_Priveleged_Member = msg
    End Function
#End Region
#Region "WebMethod Command_Delete"
    <WebMethod()> Public Function Command_Delete(ByVal command As String) As String


        Try
            LoadBasics()
            params.Add(command)
            DB.GetStoredProcedure("Command_Delete", params)
            msg = "Command " & command & " verwijderd"
        Catch ex As Exception
            msg = "Command " & command & " verwijderen mislukt : Check Log.txt"
            logger.WriteLine("WebMethod Command_Delete", ex.ToString)
        End Try

        Command_Delete = msg
    End Function
#End Region
#Region "WebMethod Web_CheckIfUserExists"
    <WebMethod()> Public Function Web_CheckIfUserExists(ByVal user As String) As Boolean

        Try
            LoadBasics()
            params.Add(user)
            ds = DB.GetStoredProcedure("Web_CheckIfUserExists", params)
        Catch ex As Exception
            logger.WriteLine("WebMethod Web_CheckIfUserExists", ex.ToString)
            Web_CheckIfUserExists = True
            Exit Function
        End Try

        If (ds.Tables.Count > 0) Then
            Try
                If (ds.Tables(0).Rows(0)("WebUserName").ToString.Equals(user)) Then
                    Web_CheckIfUserExists = True
                Else
                    Web_CheckIfUserExists = False
                End If
            Catch ex As Exception
                Web_CheckIfUserExists = False
            End Try
        Else
            Web_CheckIfUserExists = False
        End If
    End Function
#End Region
#Region "Web_GetPassword"
    <WebMethod()> Public Function Web_GetPassword(ByVal user As String) As String

        Try
            LoadBasics()
            params.Add(user)
            list = DataSetToListOfObject(DB.GetStoredProcedure("Web_GetPassword", params))
        Catch ex As Exception
            logger.WriteLine("WebMethod Web_GetPassword", ex.ToString)
            Web_GetPassword = Nothing
            Exit Function
        End Try

        Web_GetPassword = list.Item(0).ToString
    End Function
#End Region
#Region "Web_RegisterUser"
    <WebMethod()> Public Function Web_RegisterUser(ByVal user As String, password As String, email As String, fullname As String, country As String, pid As Integer) As String

        Try
            LoadBasics()
            With params
                .Add(user)
                .Add(password)
                .Add(email)
                .Add(fullname)
                .Add(country)
                .Add(pid)
            End With
            DB.GetStoredProcedure("Web_RegisterUser", params)
            msg = "User " & user & " is succesvol toegevoegd"
        Catch ex As Exception
            msg = "Er is een fout opgetreden bij het toevoegen van User " & user & " : Check Log.txt"
            logger.WriteLine("WebMethod Web_RegisterUser", ex.ToString)
        End Try
        Web_RegisterUser = msg
    End Function
#End Region
#Region "WebMethod Get_Commands"
    <WebMethod(), XmlInclude(GetType(Commands))> Public Function Get_Commands(ByVal access_lvl As Integer) As List(Of Object)
        Dim command As New Commands()



        Try
            LoadBasics()
            params.Add(access_lvl)
            ds = DB.GetStoredProcedure("Get_Commands", params)
            For Each Row As DataRow In ds.Tables.Item(0).Rows
                command.Access_lvl = Row.Item("Access_lvl")
                command.Command_Name = Row.Item("Command_Name")
                list.Add(command)
            Next
        Catch ex As Exception
            logger.WriteLine("WebMethod Get_Commands", ex.ToString)
            Get_Commands = Nothing
            Exit Function
        End Try

        Get_Commands = list




    End Function
    ''Dim ds As DataSet = DB.GetStoredProcedure(actions.Item(10))
    'Dim Command As New Commands()
    '            
#End Region
#Region "WebMethod Get_Allies"


    <WebMethod(), XmlInclude(GetType(Alliance))> Public Function Get_Allies() As List(Of Object)
        Try
            LoadBasics()

            ds = DB.GetStoredProcedure("Get_Allies")
            Dim Alliance As New Alliance
            For Each Row As DataRow In ds.Tables.Item(0).Rows
                Alliance.AID = Row.Item("aid")
                Alliance.Name = Row.Item("alliance")

                list.Add(Alliance)
            Next
        Catch ex As Exception
            logger.WriteLine("WebMethod Get_Villages", ex.ToString)
            Get_Allies = Nothing
            Exit Function
        End Try

        Get_Allies = list
    End Function
#End Region
#Region "WebMethod PopulateDB"
    'Populates de DB met de Travian dump
    <WebMethod()> Public Function PopulateDB() As String
        Dim msg As String, path As String

        path = "C:\\map.sql"

        Try
            LoadBasics()
            Fetch.Download("http://ts1.travian.nl/map.sql", path)
            DB.ExecuteSQLFile(path)
            msg = "PopulateDB gelukt !"
        Catch ex As Exception
            msg = "PopulateDB mislukt : Check Log.txt "
            logger.WriteLine("WebMethod PopulateDB", ex.ToString)
        End Try
        PopulateDB = msg
    End Function
#End Region

End Class
